package com.dao.lzj;

import com.bean.lzj.Car;
import com.bean.lzj.TruckSpace;
import com.bean.lzj.Materiel;
import com.util.lzj.DBUtil;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
@Repository
public class CarDao {
    /**
     * 查询全部车辆信息
     *
     * @return
     * @throws SQLException
     */
    public static List<Car> getcarl() throws SQLException {
        List<Car> cLList = new ArrayList<>();
        String sql = "select * from car";
        DBUtil dbUtil = new DBUtil();
        PreparedStatement ps = dbUtil.getPreparedStatement(sql);
        ResultSet rs = ps.executeQuery();
        System.out.println(11112);
        while (rs.next()) {
            Car car = new Car();
            car.setCcid(Integer.parseInt(rs.getString("ccid")));
            car.setAid(rs.getInt("aid"));
            car.setPid(rs.getInt("pid"));
            System.out.println(rs.getInt("pid"));
            car.setStopid(rs.getInt("stopid"));

            car.setCnumber(rs.getString("cnumber"));
            car.setCtel(rs.getString("ctel"));
            car.setCname(rs.getString("cname"));
            car.setCxcz(rs.getInt("cxcz"));
            cLList.add(car);
        }
        return cLList;
    }


    /**
     * 根据id删除信息
     *
     * @param ccid
     * @return
     */
    public static boolean deleteClxxById(int ccid,int carid) throws SQLException {
       System.out.println("车位号"+carid);
        DBUtil dbutil = new DBUtil();
        String sql = "delete from car where ccid = ?";
        PreparedStatement ps = dbutil.getPreparedStatement(sql);
            ps.setInt(1, ccid);
            int result = ps.executeUpdate();
            if (result == 1) {
                String sql2="UPDATE `project2`.`parking_place`SET`sid` = ? WHERE `pid` = ?";
                ps = dbutil.getPreparedStatement(sql2);
                ps.setString(1, "n");
                ps.setInt(2, carid);
                result = ps.executeUpdate();
                if (result == 1) {
                    System.out.println("修改成功");
                    System.out.println("添加成功");
                    return true;


                }
            } else {
                return false;
            }

        return false;
    }

    /**
     * 综合模糊查询动态拼接sql
     *
     * @return
     */
    public static List<Car> getzhxxcx(String czname, String czsjh, String cphm) throws SQLException {
/**
 * 动态添加sql
 */
        System.out.println(czname);
        System.out.println(czsjh);
        System.out.println(cphm);
/**
 * 动态拼接sql
 */
        String sql = "select * from car where 1=1 ";
        if (czname != null && !"".equals(czname)) {
            sql = sql + " and cname ='" + czname + "'";
            System.out.println(1);
        }
        if (czsjh != null && !"".equals(czsjh)) {
            sql = sql + " and ctel ='" + czsjh + "'";
            System.out.println(2);
        }
        if (cphm != null && !"".equals(cphm)) {
            sql = sql + " and cnumber ='" + cphm + "'";
            System.out.println(3);
        }
        DBUtil dbUtil = new DBUtil();
        List<Car> cLList = new ArrayList<>();
        System.out.println(sql);

        PreparedStatement ps = dbUtil.getPreparedStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Car car = new Car();
            car.setCcid(Integer.parseInt(rs.getString("ccid")));
            car.setAid(rs.getInt("aid"));
            car.setPid(rs.getInt("pid"));

            car.setStopid(rs.getInt("stopid"));

            car.setCnumber(rs.getString("cnumber"));
            car.setCtel(rs.getString("ctel"));
            car.setCname(rs.getString("cname"));
            car.setCxcz(rs.getInt("cxcz"));
            cLList.add(car);
        }
        return cLList;
    }


    public static List<TruckSpace> getzhxxcx(int pid) throws SQLException {
        int cwid = pid;
        System.out.println(cwid);
        List<TruckSpace> cwList = new ArrayList<>();
        DBUtil dbutil = new DBUtil();
        String sql = "select * from parking_place AS p,village AS v where p.vid = v.vid  AND p.pid=?";

        PreparedStatement ps = dbutil.getPreparedStatement(sql);
        ps.setString(1, String.valueOf(cwid));
        ResultSet rs = ps.executeQuery();


        while (rs.next()) {
            TruckSpace cw = new TruckSpace();
            cw.setPid(Integer.parseInt(rs.getString("p.Pid")));
            cw.setVid(rs.getString("p.Vid"));
            cw.setTid(rs.getString("p.tid"));
            System.out.println(rs.getString("p.sid"));
            cw.setSid(rs.getString("p.sid"));
            cw.setVname(rs.getString("v.vname"));
            cw.setParea(rs.getString("p.parea"));
            cw.setRemark(rs.getString("p.remark"));
            cw.setCaid(rs.getInt("p.caid"));

            if (cw.sid.equals("y")) {
                cw.sid = "占用";
            } else {
                cw.sid = "空闲";
            }
            cwList.add(cw);
        }

        return cwList;
    }

    /**
     * 查询住户信息
     * @return
     */
    public static List<Materiel> addzhuhuxinxi() throws SQLException {
        List<Materiel> materielList = new ArrayList<>();
        String sql = "select * from household";
        DBUtil dbUtil = new DBUtil();
        PreparedStatement ps = dbUtil.getPreparedStatement(sql);
        ResultSet rs = ps.executeQuery();
        System.out.println("查询住户信息");
        while (rs.next()) {
            Materiel zh = new Materiel();
            zh.setHhname(rs.getString("hname"));
            materielList.add(zh);
        }
        return materielList;
    }



    /**
     * 添加信息
     * @param zhxm
     * @param cwh
     * @param tckh
     * @param sjh
     * @param xszh
     * @param cph
     * @return
     */
    public static boolean tianjiaclxx(String zhxm, String cwh, String tckh, String sjh, String xszh, String cph) {
        String sql = "select * from household WHERE hname=?";
        DBUtil dbUtil = new DBUtil();
        PreparedStatement ps = dbUtil.getPreparedStatement(sql);
        String bh;
        try {
            ps.setString(1, zhxm);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Materiel zh = new Materiel();
                zh.setAid(rs.getInt("aid"));
                System.out.println(zh.aid);

                DBUtil dbutil = new DBUtil();
                String sql1 = "INSERT INTO `project2`.`car`(`ccid`, `aid`, `pid`, `stopid`, `cname`, `ctel`, `cnumber`,`cxcz`) VALUES (null, ?, ?, ?, ?, ?, ?,?)";
                ps = dbUtil.getPreparedStatement(sql1);
                try {

                    ps.setString(1, String.valueOf(zh.aid));
                    ps.setString(2, cwh);
                    ps.setString(3, tckh);
                    ps.setString(4, zhxm);
                    ps.setString(5, sjh);
                    ps.setString(6, cph);
                    ps.setString(7, xszh);


                    int result = ps.executeUpdate();
                    if (result == 1) {

                        String sql2="UPDATE `project2`.`parking_place`SET`sid` = ? WHERE `pid` = ?";
                        ps = dbutil.getPreparedStatement(sql2);
                        ps.setString(1, "y");
                        ps.setInt(2, Integer.parseInt((cwh)));
                        result = ps.executeUpdate();
                        if (result == 1) {
                            System.out.println("修改成功");
                            System.out.println("添加成功");
                            return true;


                        }


                    } else {
                        System.out.println("添加失败");
                        return false;
                    }

                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }





        return false;
    }


    public boolean deleteClxx(int ccid) {
        DBUtil dbutil = new DBUtil();
        String sql = "delete from car where ccid = ?";
        PreparedStatement ps = dbutil.getPreparedStatement(sql);
        try {
            ps.setInt(1, ccid);
            int result = ps.executeUpdate();
            if (result == 1) {
                return true;
            } else {
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();


        }
        return false;
    }
}
